Introduction to Data Blending in Tableau

Course: Tableau Training | Topic: Introduction to Data Blending | Level: Beginner to Intermediate

Learning Objectives: Understand data blending, learn why it is required, identify primary and secondary data sources, understand linking fields, create a basic blend, compare blending with joins and relationships, and troubleshoot common blending problems.

1. What Is Data Blending?

Data blending is a Tableau feature that allows you to analyze data from two or more separate data sources in the same worksheet or visualization. Tableau queries each source separately, aggregates the results to a suitable level of detail, and combines the results visually in the view.

For example, a company may store sales data in an Excel file and sales target data in a separate database. Data blending allows an analyst to compare actual sales with targets by region without first merging the original datasets into one physical table.

According to Tableau's documentation, blending is especially useful when the linking fields need to vary from one worksheet to another or when combining published data sources.

Official reference: Tableau - Blend Your Data

Simple Example

Suppose you have two datasets:

By blending these sources on Region, you can compare actual sales with targets and calculate whether each region achieved its goal.

2. Why Is Data Blending Required?

Organizations often store data in different files, applications, databases, and reporting systems. An analyst may need to compare these datasets to answer business questions.

• Combining different sources: Analyze information stored in Excel, text files, databases, and published data sources.
• Comparing performance: Compare actual sales with targets, budgets, forecasts, or quotas.
• Supporting different levels of detail: Compare summarized data from one source with detailed or differently aggregated data from another.
• Flexible worksheet-level analysis: Use different linking fields in different worksheets when needed.
• Working with published sources: Blend independently published data sources without physically joining their underlying tables.
• Reducing data preparation: Explore information from separate systems without first constructing a single merged table.

3. Important Terminology in Data Blending

4. Primary and Secondary Data Sources

4.1 Primary Data Source

The primary data source is the source whose field is used first to build the worksheet. It establishes the main structure of the visualization. In a blended view, the primary source often determines which dimension members appear.

For example, if the primary source contains data only for East and West, the view may not display North from the secondary source when North has no corresponding member in the primary source.

4.2 Secondary Data Source

The secondary data source supplies additional dimensions or measures to the existing view. Tableau aggregates the secondary data and matches it to the primary data through the configured linking fields.

4.3 How to Identify Them

• The primary data source is generally indicated by a blue check mark in the Data pane.
• The secondary data source is generally indicated by an orange check mark.
• Fields from the secondary source used in the view are marked to indicate their secondary-source origin.
• The exact interface appearance can vary by Tableau version.

Important: The first source used in the worksheet becomes primary. If you rebuild the view using a different source first, the results can change.

5. Understanding Linking Fields

A linking field is a common dimension or a pair of corresponding fields used to match values between the primary and secondary sources. The fields do not always need identical names, but they must contain compatible values that represent the same business concept.

Example

If the field names are identical, Tableau may identify the link automatically. When names differ, define the field mapping manually.

Multiple Linking Fields

Sometimes one field is not sufficient. For example, a monthly regional target dataset may require both Region and Month as linking fields. Linking only on Region could incorrectly associate a monthly target with the wrong period.

6. How Data Blending Works

1. Connect Tableau to two or more separate data sources.
2. Use a field from the first source to create a worksheet. This source becomes primary.
3. Switch to the secondary source and check for an appropriate linking field.
4. Activate or configure the link between corresponding fields.
5. Add a dimension or measure from the secondary source to the same worksheet.
6. Tableau queries the sources separately and aggregates their results.
7. Tableau combines the aggregated results in the visualization using the active links.

Conceptual flow:

Primary Data Source  --------\
                              \
                               > Match linking fields
                              /          |
Secondary Data Source -------/           v
                                 Aggregate source results
                                           |
                                           v
                               Combined worksheet visualization

This is a conceptual representation of the process, not a physical database join.

7. Step-by-Step Practical: Blend Sales and Target Data

Scenario

You work as a data analyst for a retail company. Sales figures are stored in an Excel file, while monthly targets are stored in a separate file. Your manager wants to compare actual sales with targets by region.

Sample Dataset 1: Sales.xlsx

Sample Dataset 2: Targets.xlsx

Procedure

1. Open Tableau Desktop or a supported Tableau authoring environment.
2. Connect to Sales.xlsx using the appropriate file connector.
3. Add Targets.xlsx as a separate data source using Data > New Data Source.
4. Open a new worksheet and select the Sales data source.
5. Drag Region to Rows.
6. Drag Sales to Columns. Tableau creates the initial visualization using the primary source.
7. Switch to the Targets data source in the Data pane.
8. Check whether Tableau has automatically linked Region to Region. If not, configure the link using the blend relationship settings.
9. Drag Target from the secondary source into the worksheet, such as onto the view or an appropriate Marks shelf.
10. Verify that both measures are displayed at the intended regional level.
11. Format the measures as currency and select a suitable chart type, such as side-by-side bars.

Expected Result

Note: The table above shows the expected result using the sample data. Actual results depend on correctly configured links, compatible data types, aggregation, and matching field values.

8. Creating a Calculated Field for Performance Analysis

After blending the data, you can calculate the difference between sales and target using the measures from the two sources.

8.1 Sales Difference

SUM([Sales]) - SUM([Target])

This calculation measures the absolute amount by which sales are above or below the target. The fields must be available in the worksheet and compatible with the blend's aggregation requirements.

8.2 Target Achievement Percentage

SUM([Sales]) / SUM([Target]) * 100

For the East region, target achievement is approximately 111.11%. Format the resulting field as a percentage carefully: if you multiply by 100 in the calculation, use a numeric format rather than applying percentage formatting a second time.

8.3 Performance Status

IF SUM([Sales]) >= SUM([Target]) THEN "Target Achieved"
ELSE "Target Not Achieved"
END

Use this calculated field to classify regional performance. If Tableau reports an aggregation error, review whether all measures are aggregated consistently and whether the secondary-source fields meet blending calculation requirements.

9. Data Blending vs. Joins vs. Relationships

These techniques all help combine data, but they behave differently and suit different situations.

Best practice: Tableau generally recommends considering relationships first for combining tables within a data model. Use joins when a fixed row-level combination is needed. Choose blending when its worksheet-level behavior or supported data-source scenario specifically meets your needs.

Official reference: Tableau - Plan the Data Source

10. Advantages of Data Blending

• Combines information from separate data sources in one view.
• Allows the linking fields to vary by worksheet.
• Can work with independently published data sources.
• Can support comparisons between data sources with different levels of detail.
• Does not require physically merging the original source tables.
• Useful for comparing actual results with targets, budgets, forecasts, and benchmarks.
• Allows analysts to retain separate source connections while exploring combined insights.

11. Limitations of Data Blending

• Primary-source dependency: The primary source can determine which dimension members appear in the view.
• Unmatched secondary values: Secondary-source members without corresponding primary members may not appear.
• Aggregation behavior: Tableau aggregates data from each source before combining the results, so the view may not behave like a row-level join.
• Calculation restrictions: Some non-additive aggregations and calculations have limitations in blended views.
• Filtering limitations: Filtering across secondary data sources has restrictions; some scenarios are better handled with relationships or joins.
• Publishing: A blended result cannot be published as a single new blended data source. The individual sources must be managed and published separately as appropriate.
• Performance: Querying multiple sources can affect performance depending on source systems, network, and query complexity.
• Data quality: Inconsistent labels, date granularity, or data types can prevent correct matching.

Official references: Blending Data and Filtering Across Data Sources.

12. Common Data Blending Problems and Solutions

13. Best Practices for Data Blending

1. Identify the business question before choosing a data-combination method.
2. Verify that linking fields represent the same concept in both sources.
3. Check for spelling differences, extra spaces, inconsistent capitalization, and different data types.
4. Use multiple linking fields when the business key requires them, such as Region and Month.
5. Confirm which source is primary before interpreting the results.
6. Compare source totals with the blended view to validate aggregations.
7. Do not assume a blend is equivalent to a row-level join.
8. Test calculations for both matched and unmatched values.
9. Use relationships where they provide a more flexible and accurate model for combining tables.
10. Document the data sources, linking fields, assumptions, and known limitations.

14. Real-World Applications of Data Blending

15. Practice Exercises

1. Sales and target comparison: Blend sales data and target data using Region. Create a chart comparing actual sales and target.
2. Monthly budget analysis: Use Month and Department as linking fields to compare expenses with budgets.
3. Marketing analysis: Combine campaign results with advertising expenditure and calculate cost per conversion.
4. Missing data investigation: Remove one region from the primary source and observe how the blended view changes.
5. Different field names: Link Region in one source to Territory in another by defining a custom relationship.
6. Method selection: Decide whether blending, relationships, or joins best fit a scenario with tables at different levels of detail, and explain why.

16. Interview Questions and Answers

Q1. What is data blending in Tableau?

Answer: Data blending combines data from separate data sources in one worksheet. Tableau queries the sources separately, aggregates their results, and combines the results visually using linking fields.

Q2. What is a primary data source?

Answer: It is the source used first to build the worksheet. It establishes the main view and can influence which dimension members appear.

Q3. What is a secondary data source?

Answer: It is an additional data source whose dimensions or measures are added to the view and matched to the primary source through linking fields.

Q4. What is a linking field?

Answer: A linking field is a common or mapped field that identifies corresponding values between data sources, such as Region in both sales and target datasets.

Q5. Does data blending physically join tables?

Answer: No. The sources remain separate. Tableau queries them independently and combines aggregated results in the visualization.

Q6. What is the difference between blending and joining?

Answer: Blending combines results from separate data sources at the worksheet level, while joining merges tables into a fixed row-level table in the data model.

Q7. Can linking fields have different names?

Answer: Yes. You can define a custom mapping between corresponding fields in the primary and secondary sources.

Q8. Why are some secondary-source values missing?

Answer: The values may not match the linking fields, the link may be inactive, or the primary source may not contain the relevant members.

Q9. Can Tableau blend more than two data sources?

Answer: Yes. A worksheet can use a primary source and additional secondary sources, provided the relationships and fields are configured appropriately.

Q10. When should relationships be preferred?

Answer: Relationships are often preferable when combining tables within a data model, especially when the tables have different levels of detail and Tableau needs to adapt the query to the view.

Q11. What happens if the primary source is changed?

Answer: The view may show different dimension members or results because the primary source helps determine the structure of the blended view.

Q12. Can a blended data source be published as one combined source?

Answer: The blended result itself cannot be published as a single new blended data source. The individual sources can be published and then blended in a workbook where supported.

17. Quick Revision Checklist

• Data blending combines separate sources in a worksheet.
• The first source used in the view becomes primary.
• Additional sources act as secondary sources.
• Linking fields connect corresponding values.
• Tableau queries and aggregates the sources separately.
• Blending is not the same as a physical join.
• Primary-source selection can affect which dimension members appear.
• Check field mappings, data types, date granularity, and aggregation.
• Consider relationships first for many common multi-table data models.
• Validate blended totals before presenting business conclusions.

18. Conclusion

Data blending is an important Tableau technique for analyzing information stored in separate data sources. By understanding primary and secondary sources, linking fields, aggregation, and worksheet-level behavior, analysts can compare measures such as actual sales, targets, budgets, and forecasts in a single view. Successful blending requires careful validation of matching values and an understanding of when relationships or joins would be more appropriate.

19. Tableau Training and Course Demo

Learn more about Tableau concepts, dashboards, calculations, and data analysis through the following course resources:

• Explore Tableau Training
• Register for a Tableau Course Demo

20. Additional Learning Resources

• Official Tableau Guide: Blend Your Data
• Official Tableau Guide: Plan the Data Source
• Official Tableau Guide: Relate Your Data
• Official Tableau Guide: Filter Across Data Sources